DBCC Recover From Database Corruption

Hardware failures can result in databases that are corrupt and will not open upon restart of the server. In some cases the database is marked suspect, and then cannot be opened.

The best way to deal with a database in this state is to repair it and reload it from a backup.

The MSDN's article about the command "DBCC CHECKDB" explains three ways to perform a database repair in the syntax section:

- REPAIR_ALLOW_DATA_LOSS
- REPAIR_FAST
- REPAIR_REBUILD

To repair a suspect database use following script:

DBCC CHECKDB(databaseName, repair)




REPAIR is simply the short form of REPAIR_ALLOW_DATA_LOSS.

Following are DBCC repair options that should be placed in place of repair word:

1. Repair_Rebuild
If a user wants to repair SQL database without any loss of information, then this repair option is selected.

2. Repair_Allow_Data_Loss:
In some cases users take help of repair_allow_data_loss command to repair all type of errors in the SQL database. The database should be set on single user mode to run REPAIR_ALLOW_DATA_LOSS. You cannot set the database in single user mode with all users connected, and DBCC REPAIR_ALLOW_DATA_LOSS will fail if the database is not in single user mode. It is pretty much clear from the name that there might be some loss of data during the recovery process. This means this DBCC CHECKDB command does not promise data integrity.

3. REPAIR_FAST:
Does minor fast repairs on database without risk of data loss such as repairing extra keys in nonclustered indexes.

Use DBCC statement in the third line in the script:

1. ALTER DATABASE databaseName SET EMERGENCY;
2. ALTER DATABASE databaseName SET SINGLE_USER;
3. DBCC CHECKDB (databaseName, repair) ALL_ERRORMSGS
4. ALTER DATABASE databaseName SET MULTI_USER;
5. ALTER DATABASE databaseName SET ONLINE;